{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import query_builder\n",
    "from pymongo import MongoClient\n",
    "from tqdm import tqdm\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connect to MongoDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ping Successful! You are connected to MongoDB!\n",
      "GroceryDB Permissions:\n",
      "xgen-readAtGroceryDB.CleanedData\n",
      "xgen-readAtGroceryDB.ProductIngredients\n"
     ]
    }
   ],
   "source": [
    "DB_mongo=query_builder.get_mongo_databases(verbose=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading the CleanedData dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['non-food', 'multi-items', 'alcohol', 'exempt', 'no-category', 'maybe-bug', 'find-category']\n"
     ]
    }
   ],
   "source": [
    "#Categories to remove for the GroceryDB analysis\n",
    "print(query_builder.bad_categories_more_strict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "50591it [00:17, 2895.82it/s]\n"
     ]
    }
   ],
   "source": [
    "#Fetch Data!\n",
    "gdb=[]\n",
    "for p in tqdm(DB_mongo['GroceryDB']['CleanedData'].find(\n",
    "    {\"harmonized single category\": {\"$nin\": query_builder.bad_categories_more_strict}})):\n",
    "    gdb.append(p)\n",
    "    pass\n",
    "gdb = pd.DataFrame(gdb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>original_ID</th>\n",
       "      <th>name</th>\n",
       "      <th>store</th>\n",
       "      <th>brand</th>\n",
       "      <th>url</th>\n",
       "      <th>store category</th>\n",
       "      <th>Navbar</th>\n",
       "      <th>store category cleaned str</th>\n",
       "      <th>FPro rank percentile</th>\n",
       "      <th>...</th>\n",
       "      <th>num_additives</th>\n",
       "      <th>num_amount_nodes</th>\n",
       "      <th>num_ingredients</th>\n",
       "      <th>num_marker_nodes</th>\n",
       "      <th>Nutritional_similarity_score_across_stores</th>\n",
       "      <th>fdc_id_list</th>\n",
       "      <th>sim_ingreds_oID_list_from_trees</th>\n",
       "      <th>duplicate_IDs</th>\n",
       "      <th>ignore_duplicate</th>\n",
       "      <th>tree_branch_depth_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>612d009c4c4f3be6d9e174b1</td>\n",
       "      <td>wf_green-mountain-creamery-yoyummy-yogurt-sque...</td>\n",
       "      <td>Yoyummy Yogurt Squeeze Mixed Berry, 3.5 count</td>\n",
       "      <td>WholeFoods</td>\n",
       "      <td>Green Mountain Creamery™</td>\n",
       "      <td>https://www.wholefoodsmarket.com/product/green...</td>\n",
       "      <td>[Dairy &amp; Eggs, Yogurt]</td>\n",
       "      <td>[None]</td>\n",
       "      <td>['dairy-eggs', 'yogurt']</td>\n",
       "      <td>0.992063</td>\n",
       "      <td>...</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[[tg_76201366, 0.19597153805685044], [wf_gogo-...</td>\n",
       "      <td>[[553613, 0.703875968992248, 1.0, 0.7038759689...</td>\n",
       "      <td>[[wm_949767676, 0.2523295680339483, 0, 0.25232...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>612d009c4c4f3be6d9e1756a</td>\n",
       "      <td>wf_stonyfield-yobaby-whole-milk-baby-yogurt-po...</td>\n",
       "      <td>YoBaby Whole Milk Baby Yogurt Pouches, Peach &amp;...</td>\n",
       "      <td>WholeFoods</td>\n",
       "      <td>Stonyfield</td>\n",
       "      <td>https://www.wholefoodsmarket.com/product/stony...</td>\n",
       "      <td>[None]</td>\n",
       "      <td>[None]</td>\n",
       "      <td>['none']</td>\n",
       "      <td>0.952381</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[[wf_stonyfield-organic-yobaby-whole-milk-baby...</td>\n",
       "      <td>[[1627388, 0.6301105233296711, None, 0.6301105...</td>\n",
       "      <td>[[wf_stonyfield-organic-stonyfield-organic-org...</td>\n",
       "      <td>[wf_stonyfield-organic-yobaby-whole-milk-baby-...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>612d009c4c4f3be6d9e176b0</td>\n",
       "      <td>wf_stonyfield-yobaby-whole-milk-baby-yogurt-po...</td>\n",
       "      <td>YoBaby Whole Milk Baby Yogurt Pouches, Banana ...</td>\n",
       "      <td>WholeFoods</td>\n",
       "      <td>Stonyfield</td>\n",
       "      <td>https://www.wholefoodsmarket.com/product/stony...</td>\n",
       "      <td>[None]</td>\n",
       "      <td>[None]</td>\n",
       "      <td>['none']</td>\n",
       "      <td>0.952381</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[[wf_stonyfield-organic-yobaby-whole-milk-baby...</td>\n",
       "      <td>[[1219323, 0.6438115250237989, None, 0.6438115...</td>\n",
       "      <td>[[wf_stonyfield-organic-yobaby-whole-milk-baby...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>612d009c4c4f3be6d9e179d0</td>\n",
       "      <td>wf_earths-best-stage-2-organic-pears-baby-food...</td>\n",
       "      <td>Stage 2 Organic Pears Baby Food, 4 oz</td>\n",
       "      <td>WholeFoods</td>\n",
       "      <td>Earth's Best</td>\n",
       "      <td>https://www.wholefoodsmarket.com/product/earth...</td>\n",
       "      <td>[None]</td>\n",
       "      <td>[None]</td>\n",
       "      <td>['none']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[[wf_r-w-knudsen-family-organic-pear-juice-fdf...</td>\n",
       "      <td>[[1485575, 0.977777777777778, None, 0.97777777...</td>\n",
       "      <td>[[tg_13752449, 0.567960415885582, 0, 0.5679604...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>612d009c4c4f3be6d9e186f2</td>\n",
       "      <td>wf_kite-hill-strawberry-banana-yogurt-tubes-61...</td>\n",
       "      <td>Strawberry Banana Yogurt Tubes, 14 oz</td>\n",
       "      <td>WholeFoods</td>\n",
       "      <td>Kite Hill</td>\n",
       "      <td>https://products.wholefoodsmarket.com/product/...</td>\n",
       "      <td>[dairy-eggs, dairy-eggs.dairy-alternatives, da...</td>\n",
       "      <td>[None]</td>\n",
       "      <td>['dairy-eggs', 'dairy-alternatives', 'almond',...</td>\n",
       "      <td>0.769841</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[[wf_kite-hill-artisan-almond-milk-strawberry-...</td>\n",
       "      <td>[[1348263, 0.6837226677782374, None, 0.6837226...</td>\n",
       "      <td>[[wf_kite-hill-artisan-almond-milk-strawberry-...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50586</th>\n",
       "      <td>612d009d4c4f3be6d9e25a32</td>\n",
       "      <td>tg_47110256</td>\n",
       "      <td>Hawaiian Sun Mango Pineapple Jam - 10oz</td>\n",
       "      <td>Target</td>\n",
       "      <td>Hawaiian Sun</td>\n",
       "      <td>https://www.target.com/p/hawaiian-sun-mango-pi...</td>\n",
       "      <td>[None]</td>\n",
       "      <td>[Target, Grocery, Pantry, Peanut Butter &amp; Jelly]</td>\n",
       "      <td>['grocery', 'pantry', 'peanut-butter-jelly']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[[tg_47110249, 0.0], [wm_10308106, 0.0], [tg_4...</td>\n",
       "      <td>[[1497119, 0.9726027397260276, None, 0.9726027...</td>\n",
       "      <td>[[tg_47110249, 0.5334325920667274, 0, 0.533432...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50587</th>\n",
       "      <td>612d009d4c4f3be6d9e25a33</td>\n",
       "      <td>tg_47110257</td>\n",
       "      <td>Hawaiian Sun Lite Guava Jam - 10oz</td>\n",
       "      <td>Target</td>\n",
       "      <td>Hawaiian Sun</td>\n",
       "      <td>https://www.target.com/p/hawaiian-sun-lite-gua...</td>\n",
       "      <td>[None]</td>\n",
       "      <td>[Target, Grocery, Pantry, Peanut Butter &amp; Jelly]</td>\n",
       "      <td>['grocery', 'pantry', 'peanut-butter-jelly']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>[[tg_77332753, 1.0, 0, 1.0], [wf_floral-tulips...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50588</th>\n",
       "      <td>612d009d4c4f3be6d9e25a34</td>\n",
       "      <td>tg_53847901</td>\n",
       "      <td>Kramer's Jalapeno Pepper Jam - 8oz Jar</td>\n",
       "      <td>Target</td>\n",
       "      <td>Kramer's</td>\n",
       "      <td>https://www.target.com/p/kramer-s-jalapeno-pep...</td>\n",
       "      <td>[None]</td>\n",
       "      <td>[Target, Grocery, Pantry, Peanut Butter &amp; Jelly]</td>\n",
       "      <td>['grocery', 'pantry', 'peanut-butter-jelly']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[[tg_53847900, 0.0], [tg_53847896, 0.0], [tg_5...</td>\n",
       "      <td>[[1134811, 0.9555555555555556, None, 0.9555555...</td>\n",
       "      <td>[[tg_53847900, 0.7928402227862125, 0, 0.792840...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50589</th>\n",
       "      <td>612d009d4c4f3be6d9e25a35</td>\n",
       "      <td>tg_53847896</td>\n",
       "      <td>Kramer's Cranberry Jalapeno Jam - 8 fl oz Jar</td>\n",
       "      <td>Target</td>\n",
       "      <td>Kramer's</td>\n",
       "      <td>https://www.target.com/p/kramer-s-cranberry-ja...</td>\n",
       "      <td>[None]</td>\n",
       "      <td>[Target, Grocery, Pantry, Peanut Butter &amp; Jelly]</td>\n",
       "      <td>['grocery', 'pantry', 'peanut-butter-jelly']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[[tg_53847902, 0.0], [tg_53847901, 0.0], [tg_5...</td>\n",
       "      <td>[[1134809, 0.9545454545454548, None, 0.9545454...</td>\n",
       "      <td>[[tg_53847902, 0.673525508607557, 0, 0.6735255...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50590</th>\n",
       "      <td>612d009d4c4f3be6d9e25a36</td>\n",
       "      <td>tg_53847900</td>\n",
       "      <td>Kramer's Cherry Jalapeno Jam - 8oz Jar</td>\n",
       "      <td>Target</td>\n",
       "      <td>Kramer's</td>\n",
       "      <td>https://www.target.com/p/kramer-s-cherry-jalap...</td>\n",
       "      <td>[None]</td>\n",
       "      <td>[Target, Grocery, Pantry, Peanut Butter &amp; Jelly]</td>\n",
       "      <td>['grocery', 'pantry', 'peanut-butter-jelly']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[[tg_53847901, 0.0], [tg_53847896, 0.0], [tg_5...</td>\n",
       "      <td>[[1134804, 0.9541284403669728, None, 0.9541284...</td>\n",
       "      <td>[[tg_53847901, 0.7928402227862125, 0, 0.792840...</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50591 rows × 202 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            _id  \\\n",
       "0      612d009c4c4f3be6d9e174b1   \n",
       "1      612d009c4c4f3be6d9e1756a   \n",
       "2      612d009c4c4f3be6d9e176b0   \n",
       "3      612d009c4c4f3be6d9e179d0   \n",
       "4      612d009c4c4f3be6d9e186f2   \n",
       "...                         ...   \n",
       "50586  612d009d4c4f3be6d9e25a32   \n",
       "50587  612d009d4c4f3be6d9e25a33   \n",
       "50588  612d009d4c4f3be6d9e25a34   \n",
       "50589  612d009d4c4f3be6d9e25a35   \n",
       "50590  612d009d4c4f3be6d9e25a36   \n",
       "\n",
       "                                             original_ID  \\\n",
       "0      wf_green-mountain-creamery-yoyummy-yogurt-sque...   \n",
       "1      wf_stonyfield-yobaby-whole-milk-baby-yogurt-po...   \n",
       "2      wf_stonyfield-yobaby-whole-milk-baby-yogurt-po...   \n",
       "3      wf_earths-best-stage-2-organic-pears-baby-food...   \n",
       "4      wf_kite-hill-strawberry-banana-yogurt-tubes-61...   \n",
       "...                                                  ...   \n",
       "50586                                        tg_47110256   \n",
       "50587                                        tg_47110257   \n",
       "50588                                        tg_53847901   \n",
       "50589                                        tg_53847896   \n",
       "50590                                        tg_53847900   \n",
       "\n",
       "                                                    name       store  \\\n",
       "0          Yoyummy Yogurt Squeeze Mixed Berry, 3.5 count  WholeFoods   \n",
       "1      YoBaby Whole Milk Baby Yogurt Pouches, Peach &...  WholeFoods   \n",
       "2      YoBaby Whole Milk Baby Yogurt Pouches, Banana ...  WholeFoods   \n",
       "3                  Stage 2 Organic Pears Baby Food, 4 oz  WholeFoods   \n",
       "4                  Strawberry Banana Yogurt Tubes, 14 oz  WholeFoods   \n",
       "...                                                  ...         ...   \n",
       "50586            Hawaiian Sun Mango Pineapple Jam - 10oz      Target   \n",
       "50587                 Hawaiian Sun Lite Guava Jam - 10oz      Target   \n",
       "50588             Kramer's Jalapeno Pepper Jam - 8oz Jar      Target   \n",
       "50589      Kramer's Cranberry Jalapeno Jam - 8 fl oz Jar      Target   \n",
       "50590             Kramer's Cherry Jalapeno Jam - 8oz Jar      Target   \n",
       "\n",
       "                          brand  \\\n",
       "0      Green Mountain Creamery™   \n",
       "1                    Stonyfield   \n",
       "2                    Stonyfield   \n",
       "3                  Earth's Best   \n",
       "4                     Kite Hill   \n",
       "...                         ...   \n",
       "50586              Hawaiian Sun   \n",
       "50587              Hawaiian Sun   \n",
       "50588                  Kramer's   \n",
       "50589                  Kramer's   \n",
       "50590                  Kramer's   \n",
       "\n",
       "                                                     url  \\\n",
       "0      https://www.wholefoodsmarket.com/product/green...   \n",
       "1      https://www.wholefoodsmarket.com/product/stony...   \n",
       "2      https://www.wholefoodsmarket.com/product/stony...   \n",
       "3      https://www.wholefoodsmarket.com/product/earth...   \n",
       "4      https://products.wholefoodsmarket.com/product/...   \n",
       "...                                                  ...   \n",
       "50586  https://www.target.com/p/hawaiian-sun-mango-pi...   \n",
       "50587  https://www.target.com/p/hawaiian-sun-lite-gua...   \n",
       "50588  https://www.target.com/p/kramer-s-jalapeno-pep...   \n",
       "50589  https://www.target.com/p/kramer-s-cranberry-ja...   \n",
       "50590  https://www.target.com/p/kramer-s-cherry-jalap...   \n",
       "\n",
       "                                          store category  \\\n",
       "0                                 [Dairy & Eggs, Yogurt]   \n",
       "1                                                 [None]   \n",
       "2                                                 [None]   \n",
       "3                                                 [None]   \n",
       "4      [dairy-eggs, dairy-eggs.dairy-alternatives, da...   \n",
       "...                                                  ...   \n",
       "50586                                             [None]   \n",
       "50587                                             [None]   \n",
       "50588                                             [None]   \n",
       "50589                                             [None]   \n",
       "50590                                             [None]   \n",
       "\n",
       "                                                 Navbar  \\\n",
       "0                                                [None]   \n",
       "1                                                [None]   \n",
       "2                                                [None]   \n",
       "3                                                [None]   \n",
       "4                                                [None]   \n",
       "...                                                 ...   \n",
       "50586  [Target, Grocery, Pantry, Peanut Butter & Jelly]   \n",
       "50587  [Target, Grocery, Pantry, Peanut Butter & Jelly]   \n",
       "50588  [Target, Grocery, Pantry, Peanut Butter & Jelly]   \n",
       "50589  [Target, Grocery, Pantry, Peanut Butter & Jelly]   \n",
       "50590  [Target, Grocery, Pantry, Peanut Butter & Jelly]   \n",
       "\n",
       "                              store category cleaned str  \\\n",
       "0                               ['dairy-eggs', 'yogurt']   \n",
       "1                                               ['none']   \n",
       "2                                               ['none']   \n",
       "3                                               ['none']   \n",
       "4      ['dairy-eggs', 'dairy-alternatives', 'almond',...   \n",
       "...                                                  ...   \n",
       "50586       ['grocery', 'pantry', 'peanut-butter-jelly']   \n",
       "50587       ['grocery', 'pantry', 'peanut-butter-jelly']   \n",
       "50588       ['grocery', 'pantry', 'peanut-butter-jelly']   \n",
       "50589       ['grocery', 'pantry', 'peanut-butter-jelly']   \n",
       "50590       ['grocery', 'pantry', 'peanut-butter-jelly']   \n",
       "\n",
       "       FPro rank percentile  ... num_additives num_amount_nodes  \\\n",
       "0                  0.992063  ...           9.0              0.0   \n",
       "1                  0.952381  ...           4.0              0.0   \n",
       "2                  0.952381  ...           3.0              0.0   \n",
       "3                       NaN  ...           1.0              0.0   \n",
       "4                  0.769841  ...           5.0              0.0   \n",
       "...                     ...  ...           ...              ...   \n",
       "50586                   NaN  ...           4.0              0.0   \n",
       "50587                   NaN  ...           0.0              0.0   \n",
       "50588                   NaN  ...           2.0              0.0   \n",
       "50589                   NaN  ...           2.0              0.0   \n",
       "50590                   NaN  ...           2.0              0.0   \n",
       "\n",
       "      num_ingredients num_marker_nodes  \\\n",
       "0                 8.0              0.0   \n",
       "1                10.0              0.0   \n",
       "2                 6.0              0.0   \n",
       "3                 2.0              0.0   \n",
       "4                13.0              0.0   \n",
       "...               ...              ...   \n",
       "50586             2.0              0.0   \n",
       "50587             1.0              0.0   \n",
       "50588             3.0              0.0   \n",
       "50589             3.0              0.0   \n",
       "50590             4.0              0.0   \n",
       "\n",
       "              Nutritional_similarity_score_across_stores  \\\n",
       "0      [[tg_76201366, 0.19597153805685044], [wf_gogo-...   \n",
       "1      [[wf_stonyfield-organic-yobaby-whole-milk-baby...   \n",
       "2      [[wf_stonyfield-organic-yobaby-whole-milk-baby...   \n",
       "3      [[wf_r-w-knudsen-family-organic-pear-juice-fdf...   \n",
       "4      [[wf_kite-hill-artisan-almond-milk-strawberry-...   \n",
       "...                                                  ...   \n",
       "50586  [[tg_47110249, 0.0], [wm_10308106, 0.0], [tg_4...   \n",
       "50587                                               None   \n",
       "50588  [[tg_53847900, 0.0], [tg_53847896, 0.0], [tg_5...   \n",
       "50589  [[tg_53847902, 0.0], [tg_53847901, 0.0], [tg_5...   \n",
       "50590  [[tg_53847901, 0.0], [tg_53847896, 0.0], [tg_5...   \n",
       "\n",
       "                                             fdc_id_list  \\\n",
       "0      [[553613, 0.703875968992248, 1.0, 0.7038759689...   \n",
       "1      [[1627388, 0.6301105233296711, None, 0.6301105...   \n",
       "2      [[1219323, 0.6438115250237989, None, 0.6438115...   \n",
       "3      [[1485575, 0.977777777777778, None, 0.97777777...   \n",
       "4      [[1348263, 0.6837226677782374, None, 0.6837226...   \n",
       "...                                                  ...   \n",
       "50586  [[1497119, 0.9726027397260276, None, 0.9726027...   \n",
       "50587                                               None   \n",
       "50588  [[1134811, 0.9555555555555556, None, 0.9555555...   \n",
       "50589  [[1134809, 0.9545454545454548, None, 0.9545454...   \n",
       "50590  [[1134804, 0.9541284403669728, None, 0.9541284...   \n",
       "\n",
       "                         sim_ingreds_oID_list_from_trees  \\\n",
       "0      [[wm_949767676, 0.2523295680339483, 0, 0.25232...   \n",
       "1      [[wf_stonyfield-organic-stonyfield-organic-org...   \n",
       "2      [[wf_stonyfield-organic-yobaby-whole-milk-baby...   \n",
       "3      [[tg_13752449, 0.567960415885582, 0, 0.5679604...   \n",
       "4      [[wf_kite-hill-artisan-almond-milk-strawberry-...   \n",
       "...                                                  ...   \n",
       "50586  [[tg_47110249, 0.5334325920667274, 0, 0.533432...   \n",
       "50587  [[tg_77332753, 1.0, 0, 1.0], [wf_floral-tulips...   \n",
       "50588  [[tg_53847900, 0.7928402227862125, 0, 0.792840...   \n",
       "50589  [[tg_53847902, 0.673525508607557, 0, 0.6735255...   \n",
       "50590  [[tg_53847901, 0.7928402227862125, 0, 0.792840...   \n",
       "\n",
       "                                           duplicate_IDs ignore_duplicate  \\\n",
       "0                                                   None            False   \n",
       "1      [wf_stonyfield-organic-yobaby-whole-milk-baby-...            False   \n",
       "2                                                   None            False   \n",
       "3                                                   None            False   \n",
       "4                                                   None            False   \n",
       "...                                                  ...              ...   \n",
       "50586                                               None            False   \n",
       "50587                                               None            False   \n",
       "50588                                               None            False   \n",
       "50589                                               None            False   \n",
       "50590                                               None            False   \n",
       "\n",
       "      tree_branch_depth_sum  \n",
       "0                       0.0  \n",
       "1                       2.0  \n",
       "2                       0.0  \n",
       "3                       0.0  \n",
       "4                       4.0  \n",
       "...                     ...  \n",
       "50586                   0.0  \n",
       "50587                   NaN  \n",
       "50588                   0.0  \n",
       "50589                   0.0  \n",
       "50590                   0.0  \n",
       "\n",
       "[50591 rows x 202 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading the ProductIngredients dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['non-food', 'multi-items', 'alcohol', 'exempt', 'no-category', 'maybe-bug', 'find-category']\n"
     ]
    }
   ],
   "source": [
    "#Categories to remove for the GroceryDB analysis\n",
    "print(query_builder.bad_categories_more_strict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "42204it [00:07, 5739.59it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ProductIngredient json downloaded\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "#Fetch ingredient data!\n",
    "gdbp=[]\n",
    "for p in tqdm(DB_mongo['GroceryDB']['ProductIngredients'].find(\n",
    "    {\"harmonized single category\": {\"$nin\": query_builder.bad_categories_more_strict}})):\n",
    "    gdbp.append(p)\n",
    "    pass\n",
    "print('ProductIngredient json downloaded')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to flatten json\n",
    "def flatten_ingredients(product_name,original_id,ingredients,parent_order=0,depth=1):\n",
    "    rows=[]\n",
    "\n",
    "    for ingredient in ingredients:\n",
    "        # Calculate distance to first node\n",
    "        distance_to_root=ingredient.get('order',0)+parent_order\n",
    "\n",
    "        # Extract ingredient information, calculated distance, depth, and parent_order\n",
    "        row = {\n",
    "            'product_name': product_name,\n",
    "            'original_ID': original_id,\n",
    "            'ingredient_name': ingredient.get('ingredient_name'),\n",
    "            'original_text': ingredient.get('original_text'),\n",
    "            'general_name': ingredient.get('general_name'),\n",
    "            'ingredient_type': ingredient.get('ingredient_type'),\n",
    "            'order': ingredient.get('order'),\n",
    "            'descriptors': ingredient.get('descriptors', []),\n",
    "            'parent_order': parent_order,\n",
    "            'distance_to_root': distance_to_root,\n",
    "            'depth': depth\n",
    "        }\n",
    "        rows.append(row)\n",
    "\n",
    "        # Rerun function for sub-ingredients\n",
    "        if ingredient.get('sub_ingredients'):\n",
    "            rows.extend(flatten_ingredients(product_name,original_id,\n",
    "                ingredient['sub_ingredients'],parent_order=distance_to_root,depth=depth+1\n",
    "            ))\n",
    "\n",
    "    return rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Count of all ingredients in all products:  607072\n"
     ]
    }
   ],
   "source": [
    "#Convert json into Dataframe\n",
    "all_rows=[]\n",
    "for product in gdbp:\n",
    "    product_name=product.get('product_name')\n",
    "    original_id=product.get('original_ID') \n",
    "    if product_name and product.get('ingredient_tree'):  \n",
    "        all_rows.extend(flatten_ingredients(product_name,original_id,product['ingredient_tree']))\n",
    "\n",
    "p_ingreds=pd.DataFrame(all_rows)\n",
    "print('Count of all ingredients in all products: ', len(p_ingreds))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_name</th>\n",
       "      <th>original_ID</th>\n",
       "      <th>ingredient_name</th>\n",
       "      <th>original_text</th>\n",
       "      <th>general_name</th>\n",
       "      <th>ingredient_type</th>\n",
       "      <th>order</th>\n",
       "      <th>descriptors</th>\n",
       "      <th>parent_order</th>\n",
       "      <th>distance_to_root</th>\n",
       "      <th>depth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Organic Salsa Sensation Microgreens Salad</td>\n",
       "      <td>wf_farming-turtles-inc-organic-salsa-sensation...</td>\n",
       "      <td>cilantro</td>\n",
       "      <td>cilantro</td>\n",
       "      <td>cilantro</td>\n",
       "      <td>additive</td>\n",
       "      <td>1</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Organic Salsa Sensation Microgreens Salad</td>\n",
       "      <td>wf_farming-turtles-inc-organic-salsa-sensation...</td>\n",
       "      <td>parsley</td>\n",
       "      <td>parsley</td>\n",
       "      <td>parsley</td>\n",
       "      <td>additive</td>\n",
       "      <td>2</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Organic Salsa Sensation Microgreens Salad</td>\n",
       "      <td>wf_farming-turtles-inc-organic-salsa-sensation...</td>\n",
       "      <td>arugula</td>\n",
       "      <td>arugula</td>\n",
       "      <td>arugula</td>\n",
       "      <td>ingredient</td>\n",
       "      <td>3</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Organic Salsa Sensation Microgreens Salad</td>\n",
       "      <td>wf_farming-turtles-inc-organic-salsa-sensation...</td>\n",
       "      <td>mizuna</td>\n",
       "      <td>mizuna</td>\n",
       "      <td>mizuna</td>\n",
       "      <td>ingredient</td>\n",
       "      <td>4</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Organic Salsa Sensation Microgreens Salad</td>\n",
       "      <td>wf_farming-turtles-inc-organic-salsa-sensation...</td>\n",
       "      <td>snow pea tendril</td>\n",
       "      <td>snow pea tendril</td>\n",
       "      <td>snow pea tendril</td>\n",
       "      <td>ingredient</td>\n",
       "      <td>5</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>607067</th>\n",
       "      <td>Tasty Bite Gluten Free and Vegetarian 1 Step J...</td>\n",
       "      <td>tg_14775310</td>\n",
       "      <td>spice</td>\n",
       "      <td>spice</td>\n",
       "      <td>spice</td>\n",
       "      <td>ingredient</td>\n",
       "      <td>14</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>607068</th>\n",
       "      <td>Tasty Bite Gluten Free and Vegetarian 1 Step J...</td>\n",
       "      <td>tg_14775310</td>\n",
       "      <td>chili pepper</td>\n",
       "      <td>chili pepper</td>\n",
       "      <td>chili pepper</td>\n",
       "      <td>additive</td>\n",
       "      <td>15</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>607069</th>\n",
       "      <td>Tasty Bite Gluten Free and Vegetarian 1 Step J...</td>\n",
       "      <td>tg_14775310</td>\n",
       "      <td>ginger</td>\n",
       "      <td>ginger</td>\n",
       "      <td>ginger</td>\n",
       "      <td>additive</td>\n",
       "      <td>16</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>607070</th>\n",
       "      <td>Tasty Bite Gluten Free and Vegetarian 1 Step J...</td>\n",
       "      <td>tg_14775310</td>\n",
       "      <td>garlic</td>\n",
       "      <td>garlic</td>\n",
       "      <td>garlic</td>\n",
       "      <td>additive</td>\n",
       "      <td>17</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>607071</th>\n",
       "      <td>Tasty Bite Gluten Free and Vegetarian 1 Step J...</td>\n",
       "      <td>tg_14775310</td>\n",
       "      <td>turmeric</td>\n",
       "      <td>turmeric</td>\n",
       "      <td>turmeric</td>\n",
       "      <td>additive</td>\n",
       "      <td>18</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>607072 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             product_name  \\\n",
       "0               Organic Salsa Sensation Microgreens Salad   \n",
       "1               Organic Salsa Sensation Microgreens Salad   \n",
       "2               Organic Salsa Sensation Microgreens Salad   \n",
       "3               Organic Salsa Sensation Microgreens Salad   \n",
       "4               Organic Salsa Sensation Microgreens Salad   \n",
       "...                                                   ...   \n",
       "607067  Tasty Bite Gluten Free and Vegetarian 1 Step J...   \n",
       "607068  Tasty Bite Gluten Free and Vegetarian 1 Step J...   \n",
       "607069  Tasty Bite Gluten Free and Vegetarian 1 Step J...   \n",
       "607070  Tasty Bite Gluten Free and Vegetarian 1 Step J...   \n",
       "607071  Tasty Bite Gluten Free and Vegetarian 1 Step J...   \n",
       "\n",
       "                                              original_ID   ingredient_name  \\\n",
       "0       wf_farming-turtles-inc-organic-salsa-sensation...          cilantro   \n",
       "1       wf_farming-turtles-inc-organic-salsa-sensation...           parsley   \n",
       "2       wf_farming-turtles-inc-organic-salsa-sensation...           arugula   \n",
       "3       wf_farming-turtles-inc-organic-salsa-sensation...            mizuna   \n",
       "4       wf_farming-turtles-inc-organic-salsa-sensation...  snow pea tendril   \n",
       "...                                                   ...               ...   \n",
       "607067                                        tg_14775310             spice   \n",
       "607068                                        tg_14775310      chili pepper   \n",
       "607069                                        tg_14775310            ginger   \n",
       "607070                                        tg_14775310            garlic   \n",
       "607071                                        tg_14775310          turmeric   \n",
       "\n",
       "           original_text      general_name ingredient_type  order descriptors  \\\n",
       "0               cilantro          cilantro        additive      1          []   \n",
       "1                parsley           parsley        additive      2          []   \n",
       "2                arugula           arugula      ingredient      3          []   \n",
       "3                 mizuna            mizuna      ingredient      4          []   \n",
       "4       snow pea tendril  snow pea tendril      ingredient      5          []   \n",
       "...                  ...               ...             ...    ...         ...   \n",
       "607067             spice             spice      ingredient     14          []   \n",
       "607068      chili pepper      chili pepper        additive     15          []   \n",
       "607069            ginger            ginger        additive     16          []   \n",
       "607070            garlic            garlic        additive     17          []   \n",
       "607071          turmeric          turmeric        additive     18          []   \n",
       "\n",
       "        parent_order  distance_to_root  depth  \n",
       "0                  0                 1      1  \n",
       "1                  0                 2      1  \n",
       "2                  0                 3      1  \n",
       "3                  0                 4      1  \n",
       "4                  0                 5      1  \n",
       "...              ...               ...    ...  \n",
       "607067             0                14      1  \n",
       "607068             0                15      1  \n",
       "607069             0                16      1  \n",
       "607070             0                17      1  \n",
       "607071             0                18      1  \n",
       "\n",
       "[607072 rows x 11 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p_ingreds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "165px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
